﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using DBUtility;

namespace OracleDAL.DutyAssignment
{
    public class D_Duty
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string ID)
        {
            string strSql = "select count(1) FROM DUTY_ARRANGE_NEW where id=:ID";
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50)};
            parameters[0].Value = ID;

            return OracleHelper.Exists(strSql, parameters);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,BANCI,ZHOUQI,STARTTIME,ENDTIME,UNITISN,ZDR ");
            strSql.Append(" FROM DUTY_ARRANGE_NEW ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return OracleHelper.Query(strSql.ToString());
        }

        /// <summary>
        ///  更新一条数据
        /// </summary>
        public bool Update(Model.DutyAssignment.M_Duty model)
        {
            StringBuilder strSql = new StringBuilder();
            if (model.STARTTIME.ToString() == "0001-1-1 0:00:00")
            {
                model.STARTTIME = DateTime.Now;
                if (model.ZHOUQI == "")
                {
                    model.ENDTIME = DateTime.Now.AddDays(1);
                }
                else
                {
                    double d = double.Parse(model.ZHOUQI);
                    model.ENDTIME = DateTime.Now.AddDays(d);
                }
            }

            strSql.Append("UPDATE DUTY_ARRANGE_NEW SET BANCI=:BANCI,ZHOUQI=:ZHOUQI,STARTTIME=:STARTTIME,");
            strSql.Append("ENDTIME=:ENDTIME,UNITISN=:UNITISN,ZDR=:ZDR WHERE ID=:ID");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50),
					new OracleParameter(":BANCI", OracleType.VarChar,20),
					new OracleParameter(":ZHOUQI", OracleType.VarChar,20),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIME", OracleType.DateTime),
					new OracleParameter(":UNITISN", OracleType.VarChar,50),
					new OracleParameter(":ZDR", OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.BANCI;
            parameters[2].Value = model.ZHOUQI;
            parameters[3].Value = model.STARTTIME;
            parameters[4].Value = model.ENDTIME;
            parameters[5].Value = model.UNITISN;
            parameters[6].Value = model.ZDR;

            return OracleHelper.ExecuteSql(strSql.ToString(), parameters) > 0 ? true : false;
            
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.DutyAssignment.M_Duty GetModel(string ID)
        {
            String strSql = "select * from DUTY_ARRANGE_NEW where id=:ID";
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.VarChar,50)};
            parameters[0].Value = ID;

            Model.DutyAssignment.M_Duty model = new Model.DutyAssignment.M_Duty();
            DataSet ds = OracleHelper.Query(strSql,parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.ID = ds.Tables[0].Rows[0]["ID"].ToString();
                model.BANCI = ds.Tables[0].Rows[0]["BANCI"].ToString();
                model.ZHOUQI = ds.Tables[0].Rows[0]["ZHOUQI"].ToString();
                if (ds.Tables[0].Rows[0]["STARTTIME"].ToString() != "")
                {
                    model.STARTTIME = DateTime.Parse(ds.Tables[0].Rows[0]["STARTTIME"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ENDTIME"].ToString() != "")
                {
                    model.ENDTIME = DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIME"].ToString());
                }
                model.UNITISN = ds.Tables[0].Rows[0]["UNITISN"].ToString();
                model.ZDR = ds.Tables[0].Rows[0]["ZDR"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体(需要加where关键字)
        /// </summary>
        public Model.DutyAssignment.M_Duty GetModelByWhere(string strWhere)
        {
            String strSql = "select * from DUTY_ARRANGE_NEW " + strWhere;

            Model.DutyAssignment.M_Duty model = new Model.DutyAssignment.M_Duty();
            DataSet ds = OracleHelper.Query(strSql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.ID = ds.Tables[0].Rows[0]["ID"].ToString();
                model.BANCI = ds.Tables[0].Rows[0]["BANCI"].ToString();
                model.ZHOUQI = ds.Tables[0].Rows[0]["ZHOUQI"].ToString();
                if (ds.Tables[0].Rows[0]["STARTTIME"].ToString() != "")
                {
                    model.STARTTIME = DateTime.Parse(ds.Tables[0].Rows[0]["STARTTIME"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ENDTIME"].ToString() != "")
                {
                    model.ENDTIME = DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIME"].ToString());
                }
                model.UNITISN = ds.Tables[0].Rows[0]["UNITISN"].ToString();
                model.ZDR = ds.Tables[0].Rows[0]["ZDR"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 轮班设置的预览table
        /// </summary>
        /// <param name="dutyid"></param>
        /// <returns></returns>
        public DataTable GetShiftView(string dutyid)
        {
            DataTable rtDT = new DataTable();

            DataColumn dc;
            DataRow dr;

            dc = new DataColumn("riqi",typeof(string));
            rtDT.Columns.Add(dc);

            DataTable dtBanci = new OracleDAL.DutyAssignment.D_DutyBanci().GetList("dutyid='" + dutyid + "'").Tables[0];

            for (int i = 0; i < dtBanci.Rows.Count;i++ )
            {
                dc = new DataColumn();
                dc.ColumnName = "column" + i;
                rtDT.Columns.Add(dc);
            }

            DataTable dtDetail = new OracleDAL.DutyAssignment.D_DutyDetail().GetList("dutyid='" + dutyid + "'").Tables[0];
            for (int i = 0; i < dtDetail.Rows.Count; )
            {
                dr = rtDT.NewRow();
                dr[0] = dtDetail.Rows[i]["starttime"].ToString().Split(' ')[0].ToString();
                for (int j = 0; j < dtBanci.Rows.Count; j++)
                {
                    if (i < dtDetail.Rows.Count)
                    {
                        dr[j + 1] = new OracleDAL.Sec.User().GetModel(dtDetail.Rows[i]["userisn"].ToString()).UserName;
                    }
                    i++;
                }

                rtDT.Rows.Add(dr);
            }

            return rtDT;
        }

    }
}
